USE AdventureWorks2008

--Querying metadata using System Catalog Views

SELECT * 
FROM sys.tables;

--Querying metadata using the Information Schema

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Product';

--Querying metadata using Dynamic Management Views

SELECT count(*), Command 
FROM sys.dm_exec_requests
GROUP BY Command

--Querying metadata using System Stored Procedures and Functions 

EXEC sp_columns @table_name = N'Department',
                @table_owner = N'HumanResources';

